﻿Imports MySql.Data.MySqlClient
Public Class frmrpt_bl
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim container As String = ""
    Dim rpt1 As New rpt_BL
    Dim rpt2 As New rpt_bl_2

    Private Sub CrystalReportViewer1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Load

    End Sub

    Private Sub frmrpt_bl_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If frmset_bl.page1 = "0" Then
            CrystalReportViewer1.ReportSource = rpt1
            CrystalReportViewer1.Refresh()
            rpt1.SetParameterValue("textmessage", frmset_bl.textmsg)
            If frmset_bl.mark = "0" Then
                rpt1.SetParameterValue("menuctn", "Marks & Nos." + Environment.NewLine + "Container / Seal No.")
            Else
                rpt1.SetParameterValue("menuctn", "Container / Seal No.")

            End If

            If frmset_bl.meast = "0" Then
                rpt1.SetParameterValue("meas", "Don't Show")
            ElseIf frmset_bl.meast = "1" Then
                rpt1.SetParameterValue("meas", frmset_bl.meastext)
            End If
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from booking_bl join booking on booking_bl.idbooking_bl = booking.BINV join voyage on voyage.VOYAGEID = booking.BVOYAGE  left join consignee on  consignee.consigneeid = booking_bl.conid left join shipper on shipper.SHIPPERID = booking.BSHIP  where idbooking_bl ='" & frmset_bl.invoice & "';"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())
                    rpt1.SetParameterValue("bookingno", mySqlReader("BNO"))
                    rpt1.SetParameterValue("billoflanding", mySqlReader("BLANDNO"))
                    rpt1.SetParameterValue("notify", mySqlReader("BNOTIFY"))
                    rpt1.SetParameterValue("shipname", mySqlReader("BSHIPNAME"))
                    rpt1.SetParameterValue("shipaddress", mySqlReader("SHIPADD"))
                    If mySqlReader("SHIPTELL").ToString.Length > 3 Then
                        rpt1.SetParameterValue("shiptell", "TELL : " + mySqlReader("SHIPTELL"))
                    Else
                        rpt1.SetParameterValue("shiptell", " ")
                    End If


                    If mySqlReader("shipfax").ToString.Length > 3 Then
                        rpt1.SetParameterValue("shipfax", "FAX : " + mySqlReader("SHIPFAX"))
                    Else
                        rpt1.SetParameterValue("shipfax", " ")
                    End If


                    rpt1.SetParameterValue("conname", mySqlReader("CONNAME"))
                    rpt1.SetParameterValue("conaddress", mySqlReader("CONADD"))


                    If mySqlReader("CONTELL").ToString.Length > 3 Then
                        rpt1.SetParameterValue("contell", "TELL : " + mySqlReader("CONTELL"))

                    Else
                        rpt1.SetParameterValue("contell", " ")
                    End If


                    If mySqlReader("CONFAX").ToString.Length > 3 Then
                        rpt1.SetParameterValue("confax", "FAX : " + mySqlReader("CONFAX"))
                    Else
                        rpt1.SetParameterValue("confax", " ")
                    End If


                    If mySqlReader("BTYPEMOVE") = "0" Then
                        rpt1.SetParameterValue("typemove", "X")
                        rpt1.SetParameterValue("typemove1", "")
                    Else
                        rpt1.SetParameterValue("typemove1", "X")
                        rpt1.SetParameterValue("typemove", "")
                    End If

                    rpt1.SetParameterValue("vesselname", mySqlReader("VOYVESNAMES"))
                    If mySqlReader("VOYAGEID") = "3621" Then
                        rpt1.SetParameterValue("voyage", "S362.1")
                    Else
                        rpt1.SetParameterValue("voyage", "S" + Format(mySqlReader("VOYAGEID"), "000"))
                    End If



                    If mySqlReader("place") Is DBNull.Value Then
                        rpt1.SetParameterValue("placerecieve", "")

                    Else

                        rpt1.SetParameterValue("placerecieve", mySqlReader("place"))
                        'rpt1.SetParameterValue("placerecieve", "test1")


                    End If
                    rpt1.SetParameterValue("portdischarge", "PENANG,MALAYSIA")
                    rpt1.SetParameterValue("description", mySqlReader("BDESCRIPT"))
                    rpt1.SetParameterValue("containerno", mySqlReader("BCTNNO"))
                    rpt1.SetParameterValue("grossweight", mySqlReader("BGROSS"))
                    rpt1.SetParameterValue("number_font", mySqlReader("number_word"))
                    rpt1.SetParameterValue("number", mySqlReader("number"))
                    rpt1.SetParameterValue("typectn", mySqlReader("BCTNTYPE"))


                    If frmset_bl.checkinv = "0" Then
                        If mySqlReader("billbooking") Is DBNull.Value Then
                            rpt1.SetParameterValue("invoicenum", "")
                        Else

                            rpt1.SetParameterValue("invoicenum", "***INV NO." + mySqlReader("billbooking"))
                        End If

                    Else
                        rpt1.SetParameterValue("invoicenum", "")

                    End If
                    'rpt1.SetParameterValue("invoicenum", "***INV NO." + frmset_bl.invoice)
                    rpt1.SetParameterValue("date_commit", frmset_bl.datetimebl)
                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

            rpt1.SetParameterValue("portdischarge", "PENANG,MALAYSIA")

            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from booking join borrow on booking.BOOKINGID = borrow.BOOKID join ctnmain on ctnmain.CTNMAINID = borrow.CTNID    where BOOKINGID ='" & frmset_bl.idbooking & "';"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            container = ""
            If frmset_bl.nm = "0" Then
                container = "N/M" + Environment.NewLine
            ElseIf frmset_bl.nm = "2" Then
                container = "FLEXIONE" + Environment.NewLine
            Else
                container = ""
            End If
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())
                    container += mySqlReader("CTNSTRING") + "/" + mySqlReader("CTNSEALID") + Environment.NewLine
                End While
            Catch ex As Exception

            End Try
            rpt1.SetParameterValue("container", container)
        Else
            CrystalReportViewer1.ReportSource = rpt2

            CrystalReportViewer1.Refresh()

            If frmset_bl.mark = "0" Then
                rpt2.SetParameterValue("menuctn", "Marks & Nos." + Environment.NewLine + "Container / Seal No.")
            Else
                rpt2.SetParameterValue("menuctn", "Container / Seal No.")
            End If
            If frmset_bl.meast = "0" Then
                rpt2.SetParameterValue("meas", "Don't Show")
            ElseIf frmset_bl.meast = "1" Then
                rpt2.SetParameterValue("meas", frmset_bl.meastext)
            End If
            rpt2.SetParameterValue("textmessage", frmset_bl.textmsg)
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from booking_bl join booking on booking_bl.idbooking_bl = booking.BINV join voyage on voyage.VOYAGEID = booking.BVOYAGE  left join consignee on  consignee.consigneeid = booking_bl.conid left join shipper on shipper.SHIPPERID = booking.BSHIP  where idbooking_bl ='" & frmset_bl.invoice & "';"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())
                    rpt2.SetParameterValue("bookingno", mySqlReader("BNO"))
                    rpt2.SetParameterValue("billoflanding", mySqlReader("BLANDNO"))
                    rpt2.SetParameterValue("notify", mySqlReader("BNOTIFY"))
                    rpt2.SetParameterValue("shipname", mySqlReader("BSHIPNAME"))
                    rpt2.SetParameterValue("shipaddress", mySqlReader("SHIPADD"))
                    If mySqlReader("SHIPTELL").ToString.Length > 3 Then
                        rpt2.SetParameterValue("shiptell", "TELL : " + mySqlReader("SHIPTELL"))
                    Else
                        rpt2.SetParameterValue("shiptell", " ")
                    End If


                    If mySqlReader("shipfax").ToString.Length > 3 Then
                        rpt2.SetParameterValue("shipfax", "FAX : " + mySqlReader("SHIPFAX"))
                    Else
                        rpt2.SetParameterValue("shipfax", " ")
                    End If


                    rpt2.SetParameterValue("conname", mySqlReader("CONNAME"))
                    rpt2.SetParameterValue("conaddress", mySqlReader("CONADD"))

                    If mySqlReader("CONTELL").ToString.Length > 3 Then
                        rpt2.SetParameterValue("contell", "TELL : " + mySqlReader("CONTELL"))

                    Else
                        rpt2.SetParameterValue("contell", " ")
                    End If


                    If mySqlReader("CONFAX").ToString.Length > 3 Then
                        rpt2.SetParameterValue("confax", "FAX : " + mySqlReader("CONFAX"))
                    Else
                        rpt2.SetParameterValue("confax", " ")
                    End If




                    If mySqlReader("BTYPEMOVE") = "0" Then
                        rpt2.SetParameterValue("typemove", "X")
                        rpt2.SetParameterValue("typemove1", "")
                    Else
                        rpt2.SetParameterValue("typemove1", "X")
                        rpt2.SetParameterValue("typemove", "")
                    End If

                    rpt2.SetParameterValue("vesselname", mySqlReader("VOYVESNAMES"))
                    If mySqlReader("VOYAGEID") = "3621" Then
                        rpt2.SetParameterValue("voyage", "S362.1")
                    Else
                        rpt2.SetParameterValue("voyage", "S" + Format(mySqlReader("VOYAGEID"), "000"))
                    End If

                    If mySqlReader("place") Is DBNull.Value Then
                        rpt2.SetParameterValue("placerecieve", "")

                    Else

                        rpt2.SetParameterValue("placerecieve", mySqlReader("place"))
                        'rpt1.SetParameterValue("placerecieve", "test1")


                    End If
                    rpt2.SetParameterValue("portdischarge", "PENANG,MALAYSIA")
                    rpt2.SetParameterValue("description", mySqlReader("BDESCRIPT"))
                    rpt2.SetParameterValue("containerno", mySqlReader("BCTNNO"))
                    rpt2.SetParameterValue("grossweight", mySqlReader("BGROSS"))
                    rpt2.SetParameterValue("number_font", mySqlReader("number_word"))
                    rpt2.SetParameterValue("number", mySqlReader("number"))
                    rpt2.SetParameterValue("typectn", mySqlReader("BCTNTYPE"))

                    If frmset_bl.checkinv = "0" Then
                        If mySqlReader("billbooking") Is DBNull.Value Then
                        Else

                            rpt2.SetParameterValue("invoicenum", "***INV NO." + mySqlReader("billbooking"))
                        End If

                    Else
                        rpt2.SetParameterValue("invoicenum", "")

                    End If
                    'rpt1.SetParameterValue("invoicenum", "***INV NO." + frmset_bl.invoice)
                    rpt2.SetParameterValue("date_commit", frmset_bl.datetimebl)
                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            If frmset_bl.nm = "0" Then
                rpt2.SetParameterValue("nm", "N/M" + Environment.NewLine + "TO BE CONTINUED ON ATTACHED LIST")
            ElseIf frmset_bl.nm = 2 Then
                rpt2.SetParameterValue("nm", "FLEXIONE" + Environment.NewLine + "TO BE CONTINUED ON ATTACHED LIST")
            Else
                rpt2.SetParameterValue("nm", "TO BE CONTINUED ON ATTACHED LIST")
            End If
            Dim count_line As Integer = 1
            Dim countstring As String = ""
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from booking join borrow on booking.BOOKINGID = borrow.BOOKID join ctnmain on ctnmain.CTNMAINID = borrow.CTNID    where BOOKINGID ='" & frmset_bl.idbooking & "' ;"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())
                    container += mySqlReader("CTNSTRING") + "/" + mySqlReader("CTNSEALID") + Environment.NewLine
                    countstring += count_line.ToString + "." + Environment.NewLine
                    count_line += 1
                End While
            Catch ex As Exception

            End Try
            rpt2.SetParameterValue("container", container)
            rpt2.SetParameterValue("count", countstring)


        End If


    End Sub
End Class